package com.ld.hpfs.view.excel;

import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.servlet.view.document.AbstractExcelView;

public class MdataClientMappingExcelView  extends AbstractExcelView{
	
    @SuppressWarnings("null")
	@Override
    protected void buildExcelDocument(Map model, HSSFWorkbook workbook, HttpServletRequest request,
            HttpServletResponse response) throws Exception {

        // // 取得模型数据
        // SqlRowSet table = (SqlRowSet) model.get("table");
        // String title = model.get("title").toString();

        // 取得模型数据
    	@SuppressWarnings("unchecked")
		ArrayList<HashMap<String,Object>> table = (ArrayList<HashMap<String,Object>>) model.get("table");
    	ArrayList<HashMap<String,Object>> base = new ArrayList<HashMap<String,Object>>();
    	ArrayList<HashMap<String,Object>> contact = new ArrayList<HashMap<String,Object>>();
    	int clientnum = (Integer) table.get(0).get("clientlength");
    	String language = (String) table.get(1).get("language");
    	for(int i = 0;i<clientnum;i++){
    		base.add(table.get(i+2));
    	}
    	for(int j = 2+clientnum;j<table.size();j++){
    		contact.add(table.get(j));
    	}
//    	ArrayList<HashMap<String,Object>> temp[] = new ArrayList<HashMap<String,Object>>();
    	
//    	for(int j = 0;j<11;j++){  //基本信息
//    		temp[0].add(table.get(j));
//    	}
    		HSSFSheet sheet = workbook.createSheet(); // 创建工作区
    		String titles[] = new String[18];
    		if(language.equals("en")){
    			String titles_en[] = { "Customer_code", "Customer_name","Taxpayer's identification No.","Address","Customer_openBank","Customer_bankAccount","Customer_telphone","Document_type",
    								   "Reference field 1","Reference field 2","Reference field 3","Reference field 4","Reference field 5","Reference field 6","Reference field 7",
        							   "Reference field 8","Reference field 9","Reference field 10"};   		    
    		    for(int i = 0; i <titles_en.length; i++){
    		    	titles[i] =  titles_en[i];
    		    }
    		}else if(language.equals("zh_CN")){
        		String titles_cn[] = { "客户代码", "客户名称","客户纳税人识别号","地址","客户开户行","客户账号","客户电话","发票类型"
        				,"参考字段1","参考字段2","参考字段3","参考字段4","参考字段5","参考字段6","参考字段7","参考字段8","参考字段9","参考字段10"};
        		for(int i = 0; i <titles_cn.length; i++){
    		    	titles[i] =  titles_cn[i];
    		    }
    		}
    		HSSFRow row = sheet.createRow(0);
            HSSFCellStyle items_style = workbook.createCellStyle();
            items_style.setAlignment((short) HSSFCellStyle.ALIGN_CENTER);

            HSSFFont celltbnamefont = workbook.createFont();
            celltbnamefont.setFontHeightInPoints((short) 10);

            celltbnamefont.setColor((short) (HSSFFont.COLOR_RED));
            items_style.setFont(celltbnamefont);
            items_style.setWrapText(true);

            for (int i = 0; i < titles.length; i++) {  
                HSSFCell cell = row.createCell(i);
                sheet.setColumnWidth(i, 5335);
                cell.setCellValue(titles[i]);
                cell.setCellStyle(items_style);
            }

            HSSFCellStyle datestyle = workbook.createCellStyle();
            HSSFDataFormat df = workbook.createDataFormat();
            datestyle.setDataFormat(df.getFormat("yyyy-mm-dd"));
            // 设置导出文件名
	        SimpleDateFormat now = new SimpleDateFormat("yyyyMMddHHmmss");//设置日期格式
	        String fileName = "";
	        if(language.equals("zh_CN")){
	        	fileName = "客户主数据"+"_"+now.format(new Date());
	        	fileName = URLEncoder.encode(fileName, "UTF-8");
	        }else{
	        	fileName = "Client Mdata"+"_"+now.format(new Date());
	        }
	        response.setContentType("application/vnd.ms-excel;charset=utf-8"); 
            response.setHeader("Content-disposition","inline;filename="+fileName.toString()+".xls");
            
            int rowNum = 1;
            for (HashMap<String,Object> entry : base) {
                // create the row data
                HSSFRow datarow = sheet.createRow(rowNum++);
                datarow.createCell(0).setCellValue(entry.get("code").toString());
                datarow.createCell(1).setCellValue(entry.get("client_supplier").toString());
                datarow.createCell(2).setCellValue(entry.get("tax_code").toString());
                datarow.createCell(3).setCellValue(entry.get("address").toString());
                datarow.createCell(4).setCellValue(entry.get("bank").toString());
                datarow.createCell(5).setCellValue(entry.get("bank_account").toString());
                datarow.createCell(6).setCellValue(entry.get("tel").toString());
                datarow.createCell(7).setCellValue(entry.get("doc_type").toString());
                datarow.createCell(8).setCellValue(entry.get("ck01").toString());
                datarow.createCell(9).setCellValue(entry.get("ck02").toString());
                datarow.createCell(10).setCellValue(entry.get("ck03").toString());
                datarow.createCell(11).setCellValue(entry.get("ck04").toString());
                datarow.createCell(12).setCellValue(entry.get("ck05").toString());
                datarow.createCell(13).setCellValue(entry.get("ck06").toString());
                datarow.createCell(14).setCellValue(entry.get("ck07").toString());
                datarow.createCell(15).setCellValue(entry.get("ck08").toString());
                datarow.createCell(16).setCellValue(entry.get("ck09").toString());
                datarow.createCell(17).setCellValue(entry.get("ck10").toString());
            }
//    	for(int j = 11;j<23;j++){  //联系方式
//    		temp[1].add(table.get(j));
//    	}
	    		HSSFSheet sheet2 = workbook.createSheet(); // 创建工作区
	    		String titles2[] = new String[14];
	    		if(language.equals("en")){
	    			String titles2_en[] = {"Cust_code","Receiver_companyName", "Invoice_receiverName","Receiver_address","Receiver_companyTelphone","City",
	    								   "Remark","Receiver_email","Contact_mobilephone","Reference field 1","Reference field 2","Reference field 3","Reference field 4","Reference field 5" };
	    		    for(int i = 0; i < titles2_en.length; i++){
	    		    	titles2[i] = titles2_en[i];
	    		    }
	    		}else if(language.equals("zh_CN")){
	    			String titles2_cn[] = {"客户代码","收件公司名称", "发票收件人","收件人地址","收件单位电话","城市","备注",
	    					               "收件人邮箱","联系人手机","参考字段1","参考字段2","参考字段3","参考字段4","参考字段5" };
	    			 for(int i = 0; i < titles2_cn.length; i++){
	     		    	titles2[i] = titles2_cn[i];
	    			 }
	    		}
    	        HSSFRow row2 = sheet2.createRow(0);
    	        HSSFCellStyle items_style2 = workbook.createCellStyle();
    	        items_style2.setAlignment((short) HSSFCellStyle.ALIGN_CENTER);

    	        HSSFFont celltbnamefont2 = workbook.createFont();
    	        celltbnamefont2.setFontHeightInPoints((short) 10);

    	        celltbnamefont2.setColor((short) (HSSFFont.COLOR_RED));
    	        items_style2.setFont(celltbnamefont2);
    	        items_style2.setWrapText(true);

    	        for (int i = 0; i < titles2.length; i++) {
    	            HSSFCell cell2 = row2.createCell(i);
    	            sheet2.setColumnWidth(i, 5335);
    	            cell2.setCellValue(titles2[i]);
    	            cell2.setCellStyle(items_style2);
    	        }

    	        HSSFCellStyle datestyle2 = workbook.createCellStyle();
    	        HSSFDataFormat df2 = workbook.createDataFormat();
    	        datestyle2.setDataFormat(df2.getFormat("yyyy-mm-dd"));

    	        int rowNum2 = 1;
    	        for (HashMap<String,Object> entry2 : contact) {
    	            // create the row data
    	        	if(entry2.get("main_name") == null){continue;}//异常数据：客户数据删除后，联系信息仍存在，此时找不到CUST_CODE(即main_name)，剔除
    	            HSSFRow datarow2 = sheet2.createRow(rowNum2++);
    	            datarow2.createCell(0).setCellValue(entry2.get("main_name").toString());
    	            datarow2.createCell(1).setCellValue(entry2.get("recv_company").toString());
                    datarow2.createCell(2).setCellValue(entry2.get("recv_ticket").toString());
                    datarow2.createCell(3).setCellValue(entry2.get("recv_address").toString());
                    datarow2.createCell(4).setCellValue(entry2.get("recv_tel").toString());
                    datarow2.createCell(5).setCellValue(entry2.get("city").toString());
                    datarow2.createCell(6).setCellValue(entry2.get("remark").toString());
                    datarow2.createCell(7).setCellValue(entry2.get("recv_email").toString());
                    datarow2.createCell(8).setCellValue(entry2.get("recv_phone").toString());
                    datarow2.createCell(9).setCellValue(entry2.get("ck01").toString());
                    datarow2.createCell(10).setCellValue(entry2.get("ck02").toString());
                    datarow2.createCell(11).setCellValue(entry2.get("ck03").toString());
                    datarow2.createCell(12).setCellValue(entry2.get("ck04").toString());
                    datarow2.createCell(13).setCellValue(entry2.get("ck05").toString());
                    
    	        }
/*
//        String title = "本地科目表";
//
//        // 创建工作表和标题
        HSSFSheet sheet = workbook.createSheet(); // 创建工作区
//        HSSFRow row_title = sheet.createRow(0); // 创建一行引用对象
//        HSSFFont title_font = workbook.createFont(); // 创建标题的字体
//
//        title_font.setFontHeightInPoints((short) 8);
//        title_font.setFontHeight((short) HSSFFont.BOLDWEIGHT_NORMAL);
//        title_font.setColor((short) (HSSFFont.COLOR_RED));
//
//        HSSFCellStyle title_style = workbook.createCellStyle(); // 创建样式
//        title_style.setFont(title_font);
//
//        HSSFCell cell_title = row_title.createCell(1); // 创建单元格引用对象
//        cell_title.setCellStyle(title_style);
//        cell_title.setCellValue(title);

        // 创建数据表头
        String titles[] = { "代码", "客户名/供应商","客户注释","税号","简码","地址","特殊商品名","特殊型号规格","银行账号","是否特殊商品名客户","是否特殊型号规格","是否一般纳税人" };

        HSSFRow row = sheet.createRow(0);
        HSSFCellStyle items_style = workbook.createCellStyle();
        items_style.setAlignment((short) HSSFCellStyle.ALIGN_CENTER);

        HSSFFont celltbnamefont = workbook.createFont();
        celltbnamefont.setFontHeightInPoints((short) 10);

        celltbnamefont.setColor((short) (HSSFFont.COLOR_RED));
        items_style.setFont(celltbnamefont);
        items_style.setWrapText(true);

        for (int i = 0; i < titles.length; i++) {
            HSSFCell cell = row.createCell(i);
            sheet.setColumnWidth(i, 5335);
            cell.setCellValue(titles[i]);
            cell.setCellStyle(items_style);
        }

        HSSFCellStyle datestyle = workbook.createCellStyle();
        HSSFDataFormat df = workbook.createDataFormat();
        datestyle.setDataFormat(df.getFormat("yyyy-mm-dd"));

        int rowNum = 1;
        for (HashMap<String,Object> entry : table) {
            // create the row data
            HSSFRow datarow = sheet.createRow(rowNum++);
            datarow.createCell(0).setCellValue(entry.get("code").toString());
            datarow.createCell(1).setCellValue(entry.get("client_supplier").toString());
            datarow.createCell(2).setCellValue(entry.get("client_note").toString());
            datarow.createCell(3).setCellValue(entry.get("tax_code").toString());
            datarow.createCell(4).setCellValue(entry.get("short_code").toString());
            datarow.createCell(5).setCellValue(entry.get("address").toString());
            datarow.createCell(6).setCellValue(entry.get("special_name").toString());
            datarow.createCell(7).setCellValue(entry.get("special_specification").toString());
            datarow.createCell(8).setCellValue(entry.get("bank_account").toString());
            datarow.createCell(9).setCellValue(entry.get("is_sclient").toString());
            datarow.createCell(10).setCellValue(entry.get("is_specification").toString());
            datarow.createCell(11).setCellValue(entry.get("is_normal").toString());
        }
*/
    }
}
